package database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import shared.Friend;
import shared.Pair;
import form.WindowForm.comboSelect;

import log.Log;
import log.Log.priority;


public class FromFileToDatabase
{
	public static void insertSource(String id, String name, String surname)
	{
		DatabaseClient dbc = new DatabaseClient();
		dbc.connect();
		ArrayList<Pair<String,Class<?>>> list = new ArrayList<Pair<String,Class<?>>>();
		list.add(new Pair<String,Class<?>>(id,String.class));
		list.add(new Pair<String,Class<?>>(name,String.class));		
		list.add(new Pair<String,Class<?>>(surname,String.class));				
		dbc.executeParameterizedQueryWithoutResult("insert into SOURCE_ACCOUNT values(?, ?, ?, GETDATE())", list);
		dbc.disconnect();
	}
	
	public static void insertFriend(Friend friend)
	{
		DatabaseClient dbc = new DatabaseClient();
		dbc.connect();
		ArrayList<Pair<String,Class<?>>> list = new ArrayList<Pair<String,Class<?>>>();
		list.add(new Pair<String,Class<?>>(friend.getId(),Long.class));
		list.add(new Pair<String,Class<?>>(friend.getName(),String.class));
		list.add(new Pair<String,Class<?>>(friend.getSurname(),String.class));		
		list.add(new Pair<String,Class<?>>(friend.getGender(),String.class));		
		list.add(new Pair<String,Class<?>>(friend.getBirthplace(),String.class));		
		list.add(new Pair<String,Class<?>>(friend.getCurrentplace(),String.class));		
		list.add(new Pair<String,Class<?>>(friend.getAge(),Integer.class));		
		dbc.executeParameterizedQueryWithoutResult("insert into FRIEND values(?, ?, ?, ?, ?, ?, ?)", list);
		dbc.disconnect();
	}
	
	public static void insertFriendship(String friend1, String friend2)
	{
		DatabaseClient dbc = new DatabaseClient();
		dbc.connect();	
		
		ArrayList<Pair<String,Class<?>>> list = new ArrayList<Pair<String,Class<?>>>();
		list.add(new Pair<String,Class<?>>(friend1,Long.class));
		list.add(new Pair<String,Class<?>>(friend2,Long.class));		
		dbc.executeParameterizedQueryWithoutResult("insert into FRIENDSHIP values(?, ?)", list);
		dbc.disconnect();
	}
	
	public static void insertFamily(String friend1, String friend2, String relation)
	{
		DatabaseClient dbc = new DatabaseClient();
		dbc.connect();	
		Integer relid = 0;
		try
		{
			ArrayList<Pair<String,Class<?>>> list = new ArrayList<Pair<String,Class<?>>>();
			list.add(new Pair<String,Class<?>>(relation, String.class));
			ResultSet resultSet = dbc.executeParameterizedQuery("select familyRelationId from FAMILY_RELATION where familyRelationName = ?", list);
			while(resultSet.next()) relid = resultSet.getInt("familyRelationId");
		} 
		catch (SQLException e)
		{			
			Log.console(e.getMessage(), priority.Error);
			dbc.disconnect();
		}		
		ArrayList<Pair<String,Class<?>>> list = new ArrayList<Pair<String,Class<?>>>();
		list.add(new Pair<String,Class<?>>(friend1,Long.class));
		list.add(new Pair<String,Class<?>>(friend2,Long.class));
		list.add(new Pair<String,Class<?>>(relid.toString(),Integer.class));		
		dbc.executeParameterizedQueryWithoutResult("insert into FAMILY values(?, ?, '', ?)",list);
		dbc.disconnect();		
	}
	
	public static void insertWork(String employername, String employerid, String friendid)
	{
		DatabaseClient dbc = new DatabaseClient();
		dbc.connect();		
		ArrayList<Pair<String,Class<?>>> list = new ArrayList<Pair<String,Class<?>>>();
		list.add(new Pair<String,Class<?>>(employername,String.class));
		list.add(new Pair<String,Class<?>>(employerid,Long.class));
		list.add(new Pair<String,Class<?>>(friendid,Long.class));
		
		dbc.executeParameterizedQueryWithoutResult("exec SAVE_EMPLOYMENT ?, ?, ?", list);		
		dbc.disconnect();
	}
	
	public static void insertEducation(String friendid, String schoolid, String schoolname, String schooltype, String graduation)
	{
		DatabaseClient dbc = new DatabaseClient();
		dbc.connect();	
		ArrayList<Pair<String,Class<?>>> list = new ArrayList<Pair<String,Class<?>>>();
		list.add(new Pair<String,Class<?>>(friendid,Long.class));
		list.add(new Pair<String,Class<?>>(schoolid,Long.class));
		list.add(new Pair<String,Class<?>>(schoolname,String.class));
		list.add(new Pair<String,Class<?>>(schooltype,String.class));
		list.add(new Pair<String,Class<?>>(graduation,Integer.class));
		dbc.executeParameterizedQueryWithoutResult("exec SAVE_EDUCATION ?, ?, ?, ?, ?", list);
		dbc.disconnect();
	}
	
	public static void deleteFriend(Friend friend)
	{
		DatabaseClient dbc = new DatabaseClient();
		dbc.connect();
		ArrayList<Pair<String,Class<?>>> list = new ArrayList<Pair<String,Class<?>>>();
		list.add(new Pair<String,Class<?>>(friend.getId(),Long.class));
		dbc.executeParameterizedQueryWithoutResult("delete from FRIEND where id= ?", list);
		dbc.disconnect();
	}
	
	public static void deleteFriendship(Friend friend1, Friend friend2)
	{
		DatabaseClient dbc = new DatabaseClient();
		dbc.connect();
		ArrayList<Pair<String,Class<?>>> list = new ArrayList<Pair<String,Class<?>>>();
		list.add(new Pair<String,Class<?>>(friend1.getId(),Long.class));
		list.add(new Pair<String,Class<?>>(friend2.getId(),Long.class));
		dbc.executeParameterizedQueryWithoutResult("delete from FRIENDSHIP where (id1 = ?) and (id2 = ?)", list);
		dbc.disconnect();
	}
}
